package DBLayer;

import java.sql.Connection;
import java.sql.Date;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Iterator;

import DomainLayer.Room;
import DomainLayer.Session;

public class DBSession implements IFDBSession {

	private Connection con;

	public DBSession() {
		con = DbConnection1.getInstance().getDBcon();
	}

	public ArrayList<Room> getUnoccupiedRooms(String date) {
		ArrayList<Room> listUnoccupied = new ArrayList<Room>();
		
		ResultSet results;
	

		String query = "Select Room.room_no From Room Where Room.room_no not in (Select Room.room_no From Session ,Room Where Room.room_no=Session.room_No and date='"+date+"')"; 

		System.out.println("Query Get Uoccupied Rooms: " + query);
		

		try {
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);
		

			if (results.next()) {
				listUnoccupied = buildList(results);

	

				stmt.close();




			} else {
				listUnoccupied = null;
			}
		}

		catch (Exception e) {
			System.out.println("Query exception: " + e);
		}

		return listUnoccupied;

	}

	private ArrayList<Room> buildList(ResultSet results) {

		ArrayList<Room> rooms = new ArrayList<Room>();

		try {

			rooms.add(new Room(results.getInt(1)));
			while (results.next()) {

				rooms.add(new Room(results.getInt(1)));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return rooms;
	}

	@Override
	public int createSession(Session session) {

		int rc = -1;

		String query = "INSERT INTO session (date,room_No,teacher) VALUES("
			
				+ "'"
				+ session.getDate()
				+ "','"
				+ session.getRoom().getRoom_No() + "','" + session.getTeacher() + "')";

		System.out.println("insert : " + query);

		try {
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
		} catch (SQLException ex) {
			System.out.println("error while adding session");

		}

		return rc;

	}

	@Override
	public Session findSession(String date, int room_No) {
		String wClause = " date= '" +date + "' and room_no="+room_No;
		return singleWhere(wClause);
	}

	private Session singleWhere(String wClause) {
		ResultSet results;
		Session sessionObj = new Session();

		String query = buildQuery(wClause);
		System.out.println(query);
		try { // read the session from the database
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);

			if (results.next()) {
				sessionObj = buildSession(results);
				// association is to be build

				stmt.close();

			} else { // no Session was found
				sessionObj = null;
			}
		}// end try
		catch (Exception e) {
			System.out.println("Query exception: " + e);
		}
		return sessionObj;
	}

	private Session buildSession(ResultSet results) {
		Session sessionObj = new Session();
		try { 
			sessionObj.setDate(results.getString(1));
			sessionObj.setRoom(new Room((results.getInt(2))));
			sessionObj.setTeacher(results.getString(3));
		} catch (Exception e) {
			System.out.println("error in building the customer object");
		}
		return sessionObj;
	}

	private String buildQuery(String wClause) {
		String query = "SELECT session_ID, date, room_No, teacher From Session";

		if (wClause.length() > 0)
			query = query + " WHERE " + wClause;

		return query;
	}
	
	
	

	@Override
	public void updateSession(Session session) {
		Session sessionObj = session;

		String query = "UPDATE session SET " + "date ='" + session.getDate()
				+ "', " + "room_No ='" + sessionObj.getRoom().getRoom_No() + "', "
				+ "teacher ='" + sessionObj.getTeacher() + "' " +

				" WHERE date = '" + session.getDate() + "' and room_No="+session.getRoom().getRoom_No();
		System.out.println("Update query:" + query);
		try { // update session
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			stmt.executeUpdate(query);

			stmt.close();
		}// end try
		catch (Exception ex) {
			System.out.println("Update exception in employee db: " + ex);
		}

	}

	@Override
	public int deleteSession(String date, int room_No) {

		int rc = -1;

		String query = "DELETE FROM session WHERE  date= '" + date+"' and room_No="+room_No;
				
		System.out.println(query);
		try { // delete from session
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
		} catch (Exception ex) {
			System.out.println("Delete exception in customer db: " + ex);
		}
		return (rc);
	}


	

	
}
